﻿using MySql.Data.MySqlClient;
using System;

public class StudentManagement
{
    // 数据库连接字符串
    string connectionString = "datasource=localhost;username=root;password=XYSQL;database=homework;charset=utf8";

    // 构造函数，初始化时创建表格
    public StudentManagement()
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            string[] sql = new string[]
            {
                "CREATE TABLE IF NOT EXISTS School(SchoolID INT, SchoolName CHAR(20), PRIMARY KEY(SchoolID))",
                "CREATE TABLE IF NOT EXISTS Class(ClassID INT, ClassName CHAR(20), SchoolID INT, PRIMARY KEY(ClassID))",
                "CREATE TABLE IF NOT EXISTS Student(StudentID INT, StudentName CHAR(10), Age INT, ClassID INT, PRIMARY KEY(StudentID))",
                "CREATE TABLE IF NOT EXISTS Log(Operation CHAR(100), TimeStamp DATETIME, PRIMARY KEY(TimeStamp))"
            };
            foreach (var query in sql)
            {
                ExecuteNonQuery(query, connection);
            }
        }
    }

    // 执行不返回结果的SQL语句
    private void ExecuteNonQuery(string query, MySqlConnection connection, params MySqlParameter[] parameters)
    {
        using (MySqlCommand command = new MySqlCommand(query, connection))
        {
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            command.ExecuteNonQuery();
        }
    }

    // 执行并返回单个值的SQL查询
    private object ExecuteScalar(string query, MySqlConnection connection, params MySqlParameter[] parameters)
    {
        using (MySqlCommand command = new MySqlCommand(query, connection))
        {
            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }
            return command.ExecuteScalar();
        }
    }

    // 插入学校记录
    public void InsertSchool(int schoolID, string schoolName)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            if (RecordExists("School", "SchoolID", schoolID, connection))
            {
                Console.WriteLine("School ID already exists.");
                return;
            }

            string query = "INSERT INTO School (SchoolID, SchoolName) VALUES (@schoolID, @schoolName)";
            ExecuteNonQuery(query, connection, new MySqlParameter("@schoolID", schoolID), new MySqlParameter("@schoolName", schoolName));
            LogOperation("InsertSchool", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), connection);
        }
    }

    // 插入班级记录
    public void InsertClass(int classID, string className, int schoolID)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            if (RecordExists("Class", "ClassID", classID, connection))
            {
                Console.WriteLine("Class ID already exists.");
                return;
            }

            string query = "INSERT INTO Class (ClassID, ClassName, SchoolID) VALUES (@classID, @className, @schoolID)";
            ExecuteNonQuery(query, connection, new MySqlParameter("@classID", classID), new MySqlParameter("@className", className), new MySqlParameter("@schoolID", schoolID));
            LogOperation("InsertClass", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), connection);
        }
    }

    // 插入学生记录
    public void InsertStudent(int studentID, string studentName, int age, int classID)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            if (RecordExists("Student", "StudentID", studentID, connection))
            {
                Console.WriteLine("Student ID already exists.");
                return;
            }

            string query = "INSERT INTO Student (StudentID, StudentName, Age, ClassID) VALUES (@studentID, @studentName, @age, @classID)";
            ExecuteNonQuery(query, connection, new MySqlParameter("@studentID", studentID), new MySqlParameter("@studentName", studentName), new MySqlParameter("@age", age), new MySqlParameter("@classID", classID));
            LogOperation("InsertStudent", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), connection);
        }
    }

    // 更新学生年龄
    public void UpdateStudentAge(int studentID, int newAge)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            string query = "UPDATE Student SET Age = @newAge WHERE StudentID = @studentID";
            ExecuteNonQuery(query, connection, new MySqlParameter("@newAge", newAge), new MySqlParameter("@studentID", studentID));
            LogOperation("UpdateStudentAge", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), connection);
        }
    }

    // 删除学生记录
    public void DeleteStudent(int studentID)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            if (!RecordExists("Student", "StudentID", studentID, connection))
            {
                Console.WriteLine("Student ID does not exist.");
                return;
            }

            string query = "DELETE FROM Student WHERE StudentID = @studentID";
            ExecuteNonQuery(query, connection, new MySqlParameter("@studentID", studentID));
            LogOperation("DeleteStudent", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), connection);
        }
    }

    // 根据班级ID查询学生
    public void RetrieveStudentsByClass(int classID)
    {
        string query = "SELECT * FROM Student WHERE ClassID = @classID";
        RetrieveRecords(query, new MySqlParameter("@classID", classID));
    }

    // 查询所有学校信息
    public void RetrieveAllSchools()
    {
        string query = "SELECT * FROM School";
        RetrieveRecords(query);
    }

    // 查询所有班级信息
    public void RetrieveAllClasses()
    {
        string query = "SELECT * FROM Class";
        RetrieveRecords(query);
    }

    // 查询所有学生信息
    public void RetrieveAllStudents()
    {
        string query = "SELECT * FROM Student";
        RetrieveRecords(query);
    }

    // 通用查询记录方法
    private void RetrieveRecords(string query, params MySqlParameter[] parameters)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            using (MySqlCommand command = new MySqlCommand(query, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            Console.Write(reader.GetName(i) + ": " + reader.GetValue(i) + "\t");
                        }
                        Console.WriteLine();
                    }
                }
            }
        }
    }

    // 记录操作日志
    private void LogOperation(string operation, string timestamp, MySqlConnection connection)
    {
        string logQuery = "INSERT INTO Log (Operation, TimeStamp) VALUES (@operation, @timestamp)";
        ExecuteNonQuery(logQuery, connection, new MySqlParameter("@operation", operation), new MySqlParameter("@timestamp", timestamp));
    }

    // 显示操作日志
    public void DisplayLogs()
    {
        string logQuery = "SELECT * FROM Log";
        RetrieveRecords(logQuery);
    }

    // 检查记录是否存在
    private bool RecordExists(string tableName, string columnName, int value, MySqlConnection connection)
    {
        string query = $"SELECT COUNT(*) FROM {tableName} WHERE {columnName} = @value";
        return Convert.ToInt32(ExecuteScalar(query, connection, new MySqlParameter("@value", value))) > 0;
    }
}

public class Program
{
    public static void Main()
    {
        StudentManagement studentManagement = new StudentManagement();

        while (true)
        {
            // 显示操作菜单
            Console.WriteLine("请选择要进行的操作：");
            Console.WriteLine("1. 查询所有学校信息");
            Console.WriteLine("2. 查询所有班级信息");
            Console.WriteLine("3. 查询所有学生信息");
            Console.WriteLine("4. 添加学校");
            Console.WriteLine("5. 添加班级");
            Console.WriteLine("6. 添加学生");
            Console.WriteLine("7. 修改学生年龄");
            Console.WriteLine("8. 删除学生");
            Console.WriteLine("9. 显示操作记录");
            Console.WriteLine("0. 退出程序");
            Console.Write("请选择：");
            //读取用户输入的操作编号
            int choice = int.Parse(Console.ReadLine());
            Console.Clear();
            // 根据用户输入的操作编号执行相应的操作
            switch (choice)
            {
                case 1: // 查询所有学校信息
                    studentManagement.RetrieveAllSchools();
                    Console.ReadLine();
                    Console.Clear();
                    break;

                case 2: // 查询所有班级信息
                    studentManagement.RetrieveAllClasses();
                    Console.ReadLine();
                    Console.Clear();
                    break;

                case 3: // 查询所有学生信息
                    studentManagement.RetrieveAllStudents();
                    Console.ReadLine();
                    Console.Clear();
                    break;

                case 4: // 添加学校
                    Console.WriteLine("请输入SchoolID");
                    int schoolID1 = int.Parse(Console.ReadLine());
                    Console.WriteLine("请输入SchoolName");
                    string schoolName1 = Convert.ToString(Console.ReadLine());
                    Console.Clear();
                    studentManagement.InsertSchool(schoolID1, schoolName1);
                    Console.ReadLine();
                    Console.Clear();
                    break;

                case 5: // 添加班级
                    Console.WriteLine("请输入ClassID");
                    int classID1 = int.Parse(Console.ReadLine());
                    Console.WriteLine("请输入ClassName");
                    string className1 = Convert.ToString(Console.ReadLine());
                    Console.WriteLine("请输入SchoolID");
                    int schoolID2 = int.Parse(Console.ReadLine());
                    Console.Clear();
                    studentManagement.InsertClass(classID1, className1, schoolID2);
                    Console.ReadLine();
                    Console.Clear();
                    break;

                case 6: // 添加学生
                    Console.WriteLine("请输入StudentID");
                    int studentID1 = int.Parse(Console.ReadLine());
                    Console.WriteLine("请输入StudentName");
                    string studentName1 = Convert.ToString(Console.ReadLine());
                    Console.WriteLine("请输入Age");
                    int age1 = int.Parse(Console.ReadLine());
                    Console.WriteLine("请输入ClassID");
                    int classID2 = int.Parse(Console.ReadLine());
                    Console.Clear();
                    studentManagement.InsertStudent(studentID1, studentName1, age1, classID2);
                    Console.ReadLine();
                    Console.Clear();
                    break;

                case 7: // 修改学生年龄
                    Console.WriteLine("请输入StudentID");
                    int studentID2 = int.Parse(Console.ReadLine());
                    Console.WriteLine("请输入新的Age");
                    int newAge = int.Parse(Console.ReadLine());
                    Console.Clear();
                    studentManagement.UpdateStudentAge(studentID2, newAge);
                    Console.ReadLine();
                    Console.Clear();
                    break;

                case 8: // 删除学生
                    Console.WriteLine("请输入StudentID");
                    int studentID3 = int.Parse(Console.ReadLine());
                    Console.Clear();
                    studentManagement.DeleteStudent(studentID3);
                    Console.ReadLine();
                    Console.Clear();
                    break;

                case 9: // 显示操作记录
                    studentManagement.DisplayLogs();
                    Console.ReadLine();
                    Console.Clear();
                    break;

                case 0: // 退出程序
                    return;

                default:
                    Console.WriteLine("无效的选择，请重新选择。");
                    break;
            }
        }
    }
}